{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-02T03:19:51.162210Z",
     "start_time": "2019-04-02T03:19:49.983798Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import gc\n",
    "import os \n",
    "import warnings\n",
    "import numpy as np \n",
    "import time\n",
    "from sklearn.preprocessing import OneHotEncoder,LabelEncoder\n",
    "from contextlib import contextmanager\n",
    "from lightgbm import LGBMClassifier\n",
    "from sklearn.model_selection import KFold, StratifiedKFold\n",
    "from sklearn.metrics import roc_auc_score\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.087331Z",
     "start_time": "2019-03-29T06:38:55.084280Z"
    }
   },
   "outputs": [],
   "source": [
    "# from pyspark import SparkService\n",
    "# # 创建local模式的SparkSession\n",
    "# spark = SparkService.get_local_spark(executor_instances=3, driver_mem='10g')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T09:23:30.230411Z",
     "start_time": "2019-03-29T09:23:07.261418Z"
    }
   },
   "outputs": [],
   "source": [
    "from pyspark import SparkService\n",
    "# 创建spark会话，自定义资源\n",
    "# spark = SparkService.get_spark(executor_instances=1, per_executor_mem='1g', driver_mem='1g')\n",
    "# 创建spark会话，使用默认资源\n",
    "spark = SparkService.get_spark(executor_instances=2,per_executor_mem='4g',driver_mem='2g')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.120658Z",
     "start_time": "2019-03-29T06:38:55.105160Z"
    }
   },
   "outputs": [],
   "source": [
    "# # the spark short for SparkSession and sc short for SparkContext have already declared\n",
    "# spark.sql(\"show databases\").show()\n",
    "# spark.sql(\"use qx_testing\").show()\n",
    "# spark.sql(\"show tables\").toPandas()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 重新刷一遍表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.140999Z",
     "start_time": "2019-03-29T06:38:55.123080Z"
    }
   },
   "outputs": [],
   "source": [
    "# paths='/opt/notebook/xuyinghao'\n",
    "\n",
    "# def createtTable(paths):\n",
    "#     for file in os.listdir():\n",
    "#         col_list =[]\n",
    "#         if 'csv' in file:\n",
    "#             print('table %s 正在导入'%(file.replace('.csv','')))\n",
    "#             start = time.time()\n",
    "#             dir_ = os.path.join(paths,file)\n",
    "#             df = pd.read_csv(dir_)\n",
    "#             df.replace(np.NAN,'',inplace=True)\n",
    "#             for col in df.columns:\n",
    "#                 col_list.append(col.lower())\n",
    "#             df.columns = col_list\n",
    "#             df[col_list]=df[col_list].astype(str)\n",
    "#             spark_table = spark.createDataFrame(df,verifySchema=False)\n",
    "#             del df,col_list\n",
    "#             gc.collect()\n",
    "#             spark_table.write.saveAsTable(name='qx_testing.home_credit_%s'%(file.replace('.csv','')),mode='overwrite',partitionBy=None)\n",
    "#             end = time.time()\n",
    "#             print('table %s 导入成功!'%(file.replace('.csv','')))\n",
    "#             print('导入开销%f s'%(end-start))\n",
    " \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.163016Z",
     "start_time": "2019-03-29T06:38:55.143810Z"
    }
   },
   "outputs": [],
   "source": [
    "# createtTable(paths)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 定义一些辅助函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.183519Z",
     "start_time": "2019-03-29T06:38:55.165620Z"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "def change_age_tobin(days_birth):\n",
    "    x = -days_birth / 365\n",
    "    if x < 20: return 1\n",
    "    elif x < 30: return 2\n",
    "    elif x < 40: return 3\n",
    "    elif x < 50: return 4\n",
    "    elif x < 60: return 5\n",
    "    else: return 0\n",
    "    \n",
    "def cal_mean(df, group_cols, col, agg_name):\n",
    "    \"\"\"\n",
    "    计算均值\n",
    "    \"\"\"\n",
    "    gp = df[group_cols + [col]].groupby(group_cols)[col].mean().reset_index().rename(\n",
    "        columns={col: agg_name})\n",
    "    df = df.merge(gp, on=group_cols, how='left')\n",
    "    del gp\n",
    "    gc.collect()\n",
    "    return df\n",
    "\n",
    "\n",
    "def cal_median(df, group_cols, col, agg_name):\n",
    "    \"\"\"\n",
    "    计算中位数\n",
    "    \"\"\"\n",
    "    gp = df[group_cols + [col]].groupby(group_cols)[col].median().reset_index().rename(\n",
    "        columns={col: agg_name})\n",
    "    df = df.merge(gp, on=group_cols, how='left')\n",
    "    del gp\n",
    "    gc.collect()\n",
    "    return df\n",
    "\n",
    "\n",
    "def cal_std(df, group_cols, col, agg_name):\n",
    "    \"\"\"\n",
    "    计算标准差\n",
    "    \"\"\"\n",
    "    gp = df[group_cols + [col]].groupby(group_cols)[col].std().reset_index().rename(\n",
    "        columns={col: agg_name})\n",
    "    df = df.merge(gp, on=group_cols, how='left')\n",
    "    del gp\n",
    "    gc.collect()\n",
    "    return df\n",
    "\n",
    "def onehot_label_encoder(df,categorical_columns=None):\n",
    "    \"\"\"\n",
    "    ont hot \n",
    "    \"\"\"\n",
    "    original_columns = list(df.columns)\n",
    "    if not categorical_columns:\n",
    "        categorical_columns = [col for col in df.columns if df[col].dtype == 'object']\n",
    "    df = pd.get_dummies(df, columns=categorical_columns)\n",
    "    categorical_columns = [c for c in df.columns if c not in original_columns]\n",
    "    return df,categorical_columns\n",
    "    \n",
    "def read_table(file_or_path):\n",
    "    \"\"\"\n",
    "    将列名转换为小写\n",
    "    \"\"\"\n",
    "    df = pd.read_csv(file_or_path)\n",
    "    col_list = []\n",
    "    for col in df.columns:\n",
    "        col_list.append(col.lower())\n",
    "    df.columns = col_list\n",
    "    return df \n",
    "\n",
    "def group(df_to_agg, prefix, aggregations, aggregate_by= 'sk_id_curr'):\n",
    "    \"\"\"\n",
    "     对每个表按照主键groupby\n",
    "    \"\"\"\n",
    "    agg_df = df_to_agg.groupby(aggregate_by).agg(aggregations)\n",
    "    agg_df.columns = pd.Index(['{}{}_{}'.format(prefix, e[0], e[1].lower())\n",
    "                               for e in agg_df.columns.tolist()])\n",
    "    return agg_df.reset_index()\n",
    "\n",
    "def group_and_merge(df_to_agg, df_to_merge, prefix, aggregations, aggregate_by= 'sk_id_curr'):\n",
    "    \"\"\"\n",
    "    合并groupby后的数据集\n",
    "    \"\"\"\n",
    "    agg_df = group(df_to_agg, prefix, aggregations, aggregate_by= aggregate_by)\n",
    "    return df_to_merge.merge(agg_df, how='left', on= aggregate_by)\n",
    "\n",
    "\n",
    "@contextmanager\n",
    "def timer(name):\n",
    "    t0 = time.time()\n",
    "    yield\n",
    "    print(\"{} - done in {:.0f}s\".format(name, time.time() - t0))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 处理主表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.211484Z",
     "start_time": "2019-03-29T06:38:55.185920Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_train_test():\n",
    "    df_train = read_table('application_train.csv')\n",
    "    df_test = read_table('application_test.csv')\n",
    "    df = df_train.append(df_test)\n",
    "    del df_train,df_test\n",
    "    gc.collect()\n",
    "    \n",
    "    #过滤掉异常样本以及用NAN替换掉异常值\n",
    "    df= df[df['code_gender']!='XNA']\n",
    "    df = df[df['amt_income_total']<20000000]\n",
    "    \n",
    "    df['days_employed'].replace(365243, np.nan, inplace=True)\n",
    "    df['days_last_phone_change'].replace(0, np.nan, inplace=True)\n",
    "\n",
    "    docs = [x for x in df.columns if 'flag_doc' in x]\n",
    "    df['document_count'] = df[docs].sum(axis=1)\n",
    "    df.drop(columns=docs,inplace=True)\n",
    "    df['age_bin'] = df['days_birth'].map(change_age_tobin)\n",
    "    df['ext_sources_prod'] = df['ext_source_1'] * df['ext_source_2'] * df['ext_source_3']\n",
    "    df['ext_source_weighted'] = df['ext_source_1'] * 2 + df['ext_source_1'] * 1 + df['ext_source_1'] * 3\n",
    "    \n",
    "    \n",
    "    #构建一些比例特征\n",
    "    df['credit_to_annuity_ratio'] = df['amt_credit'] / df['amt_annuity']\n",
    "    df['credit_to_goods_ratio'] = df['amt_credit'] / df['amt_goods_price']\n",
    "    # 收入类型比列\n",
    "    \n",
    "    df['own_car_age'] = pd.to_numeric(df['own_car_age'],errors='ignore')\n",
    "    df['annuity_to_income_ratio'] = df['amt_annuity'] / df['amt_income_total']\n",
    "    df['credit_to_income_ratio'] = df['amt_credit'] / df['amt_income_total']\n",
    "    df['income_to_employed_ratio'] = df['amt_income_total'] / df['days_employed']\n",
    "    df['income_to_birth_ratio'] = df['amt_income_total'] / df['days_birth']\n",
    "    # 时间序列形式比列特征\n",
    "    df['employed_to_birth_ratio'] = df['days_employed'] / df['days_birth']\n",
    "    df['car_to_birth_ratio'] = df['own_car_age'] / df['days_birth']\n",
    "    df['car_to_employed_ratio'] = df['own_car_age'] / df['days_employed']\n",
    "    \n",
    "    #统计特征\n",
    "    #重点关注EXT_SOURCE_1，EXT_SOURCE_2，EXT_SOURCE_3三个字段\n",
    "    for function_name in ['min','max','mean']:\n",
    "        feature_name = 'ext_sources_{}'.format(function_name)\n",
    "        df[feature_name] = eval('np.{}'.format(function_name))(df[['ext_source_1','ext_source_2','ext_source_3']],axis=1)\n",
    "    \n",
    "    group_col = ['organization_type', 'name_education_type', 'occupation_type', 'age_bin', 'code_gender']\n",
    "  \n",
    "    #根据group_col来计算分组后的ext_source_median\n",
    "    df=cal_median(df,group_col,'ext_sources_mean','group_ext_sources_median')\n",
    "    df =cal_std(df,group_col,'ext_sources_mean','group_ext_sources_std')\n",
    "    #计算分组后收入的平均值\n",
    "    df = cal_mean(df,group_col,'amt_income_total','group_income_mean')\n",
    "    df = cal_std(df,group_col,'amt_income_total','group_income_std')\n",
    "    #计算分组申请贷款的金额\n",
    "    df = cal_mean(df,group_col,'amt_credit','group_credit_mean')\n",
    "    df = cal_std(df,group_col,'amt_credit','group_credit_std')\n",
    "    df = cal_mean(df,group_col,'amt_annuity','group_annuity_mean')\n",
    "    df = cal_std(df,group_col,'amt_annuity','group_annuity_std')\n",
    "    \n",
    "    #变量编码\n",
    "    df,categorical_col= onehot_label_encoder(df)\n",
    "    \n",
    "    return df\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 衍生bureau_balance表数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.247039Z",
     "start_time": "2019-03-29T06:38:55.214969Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_bureau_balance():\n",
    "    df = read_table('bureau_balance.csv')\n",
    "    df, categorical_cols = onehot_label_encoder(df,categorical_columns=None)\n",
    "    # 计算各个类别所占的比例\n",
    "    bb_processed = df.groupby('sk_id_bureau')[categorical_cols].mean().reset_index()\n",
    "    agg = {'months_balance': ['min', 'max', 'mean']}\n",
    "    bb_processed = group_and_merge(df, bb_processed, '', agg, 'sk_id_bureau')\n",
    "    del df; gc.collect()\n",
    "    return bb_processed\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 衍生bureau以及合并bureau_balance表特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.277679Z",
     "start_time": "2019-03-29T06:38:55.249617Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_bureau():\n",
    "    df = read_table('bureau.csv')\n",
    "    ##衍生一些比例特征\n",
    "    #逾期金额/当前额度\n",
    "    df['credit_sum_overdue_ratio']=df['amt_credit_sum_overdue']/df['amt_credit_sum']\n",
    "    #负债金额/当前额度\n",
    "    df['debt_percentage']=df['amt_credit_sum_debt']/df['amt_credit_sum']\n",
    "    #当前金额/年金\n",
    "    df['credit_to_annuity_ratio']=df['amt_credit_sum']/df['amt_annuity']\n",
    "    \n",
    "    #onehot \n",
    "    df, categorical_cols = onehot_label_encoder(df)\n",
    "    \n",
    "    df = df.merge(process_bureau_balance(),on='sk_id_bureau',how='left')\n",
    "    #衍生一些统计特征\n",
    "    group_col=['sk_id_bureau']\n",
    "    df = cal_mean(df,group_col,'amt_credit_sum_debt','group_sum_debt_mean')\n",
    "    df= cal_std(df,group_col,'amt_credit_sum_debt','group_sum_debt_std')\n",
    "    df = cal_mean(df,group_col,'amt_credit_sum_overdue','group_sum_overdue_mean')\n",
    "    df = cal_std(df,group_col,'amt_credit_sum_overdue','group_sum_overdue_std')\n",
    "    \n",
    "    bureau_agg ={\n",
    "    'sk_id_bureau': ['nunique'],\n",
    "    'days_credit': ['min', 'max', 'mean'],\n",
    "    'days_credit_enddate': ['min', 'max'],\n",
    "    'amt_credit_max_overdue': ['max', 'mean'],\n",
    "    'amt_credit_sum': ['max', 'mean', 'sum'],\n",
    "    'amt_credit_sum_debt': ['max', 'mean', 'sum'],\n",
    "    'amt_credit_sum_overdue': ['max', 'mean', 'sum'],\n",
    "    'amt_annuity': ['mean'],\n",
    "    'amt_credit_sum_debt':['mean'],\n",
    "    'amt_credit_sum_overdue':['mean'],\n",
    "    # 类别型特征\n",
    "    'status_0': ['mean'],\n",
    "    'status_1': ['mean'],\n",
    "    'status_2': ['mean'],\n",
    "    'status_C': ['mean'],\n",
    "    'status_X': ['mean'],\n",
    "    'credit_active_Active': ['mean'],\n",
    "    'credit_active_Closed': ['mean'],\n",
    "    'credit_active_Sold': ['mean'],\n",
    "    'credit_type_Mortgage': ['mean'],\n",
    "    'credit_type_Microloan': ['mean']\n",
    "}\n",
    "    #聚合特征\n",
    "    df = group(df,prefix='bureau_',aggregations=bureau_agg)\n",
    "    \n",
    "    return df\n",
    "   \n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 衍生previous_applicaton表特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.302075Z",
     "start_time": "2019-03-29T06:38:55.280108Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_previous_application():\n",
    "    df = read_table('previous_application.csv')\n",
    "   \n",
    "    #衍生一些比例特征以及差值特征\n",
    "    df['application_credit_diff']= df['amt_application'] - df['amt_credit']\n",
    "    df['application_to_credit_ratio']= df['amt_application']/df['amt_credit']\n",
    "    df['credit_to_annuity_ratio']=df['amt_credit']/df['amt_annuity']\n",
    "    \n",
    "    group_col = ['name_client_type','name_contract_status','name_contract_type','name_cash_loan_purpose','code_reject_reason']\n",
    "    \n",
    "    df = cal_mean(df,group_col,'amt_annuity','group_annuity_mean')\n",
    "    df = cal_median(df,group_col,'amt_annuity','group_annuity_std')\n",
    "    df = cal_mean(df,group_col,'amt_credit','group_amt_credity_mean')\n",
    "    df = cal_median(df,group_col,'amt_credit','group_amt_credit_std')\n",
    "    df = cal_mean(df,group_col,'amt_application','group_amt_application_mean')\n",
    "    df = cal_median(df,group_col,'amt_application','group_amt_application_std')\n",
    "    \n",
    "    \n",
    "    #onehot编码\n",
    "    df,categorical_cols = onehot_label_encoder(df)\n",
    "    \n",
    "    \n",
    "    #将365243替换为nan\n",
    "    df['days_first_drawing'].replace(365243, np.nan, inplace= True)\n",
    "    df['days_first_due'].replace(365243, np.nan, inplace= True)\n",
    "    df['days_last_due_1st_version'].replace(365243, np.nan, inplace= True)\n",
    "    df['days_last_due'].replace(365243, np.nan, inplace= True)\n",
    "    df['days_termination'].replace(365243, np.nan, inplace= True)\n",
    "    \n",
    "    prev_agg ={\n",
    "    'sk_id_prev': ['nunique'],\n",
    "    'amt_annuity': ['min', 'max', 'mean'],\n",
    "    'amt_down_payment': ['max', 'mean'],\n",
    "    'rate_down_payment': ['max', 'mean'],\n",
    "    'days_decision': ['min', 'max', 'mean'],\n",
    "    'cnt_payment': ['max', 'mean'],\n",
    "    'days_termination': ['max'],\n",
    "    'amt_application':['max','mean'],\n",
    "    'amt_goods_price':['min','max'],\n",
    "    'days_first_drawing':['max','mean'],\n",
    "    'days_first_due':['min','mean'],\n",
    "    'days_last_due':['max','mean'],\n",
    "    'days_last_due_1st_version':['min','max','mean'],\n",
    "    # 衍生的特征\n",
    "    'credit_to_annuity_ratio': ['mean', 'max'],\n",
    "    'application_credit_diff': ['min', 'max', 'mean'],\n",
    "    'application_to_credit_ratio': ['min', 'max', 'mean'],\n",
    "    'group_annuity_mean':['mean'],\n",
    "    'group_annuity_std':['std'],\n",
    "    'group_amt_credity_mean':['mean'],\n",
    "    'group_amt_credit_std':['std'],\n",
    "    'group_amt_application_mean':['mean'],\n",
    "    'group_amt_application_std':['std'],\n",
    "     #类别型特征\n",
    "    'name_contract_type_Cash loans':['mean'],\n",
    "    'name_contract_type_Consumer loans':['mean'],\n",
    "    'name_contract_type_Revolving loans':['mean']\n",
    "      \n",
    "}\n",
    "    \n",
    "    df = group(df,prefix='prev_',aggregations=prev_agg)\n",
    "    \n",
    "    return df\n",
    "    \n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 衍生 pos_cash表特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T09:12:34.201632Z",
     "start_time": "2019-03-29T09:12:34.193117Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_pos_cash_balance():\n",
    "    df = read_table('POS_CASH_balance.csv')\n",
    "    df['late_payment']=df['sk_dpd'].map(lambda x:1 if x>0 else 0)\n",
    "    df['sk_dpd_diff']=df['sk_dpd']-df['sk_dpd_def']\n",
    "    df['instalment_diff']=df['cnt_instalment'] - df['cnt_instalment_future']\n",
    "    \n",
    "    df = cal_mean(df,['sk_id_curr'],'cnt_instalment','group_cnt_instalment_mean')\n",
    "    df = cal_std(df,['sk_id_curr'],'cnt_instalment','group_cnt_instalment_std')\n",
    "    df = cal_mean(df,['sk_id_curr'],'cnt_instalment_future','group_cnt_instalment_future_mean')\n",
    "    df = cal_std(df,['sk_id_curr'],'cnt_instalment_future','group_cnt_instalment_future_std')\n",
    "    \n",
    "    \n",
    "    pos_card_agg={\n",
    "    'sk_id_prev': ['nunique'],\n",
    "    'months_balance': ['min', 'max'],\n",
    "    'sk_dpd': ['max', 'mean', 'sum', 'var'],\n",
    "    'sk_dpd_def': ['max', 'mean', 'sum'],\n",
    "    'late_payment': ['mean'],\n",
    "    'instalment_diff':['mean','max','min'],\n",
    "    'group_cnt_instalment_mean':['mean'],\n",
    "    'group_cnt_instalment_std':['mean'],\n",
    "    'group_cnt_instalment_future_mean':['mean'],\n",
    "    'group_cnt_instalment_future_std':['mean']\n",
    "} \n",
    "    \n",
    "    df = group(df,prefix='pos_cash_',aggregations=pos_card_agg)\n",
    "    return df\n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 衍生 installments_payment表特征\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.352726Z",
     "start_time": "2019-03-29T06:38:55.328725Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_payment():\n",
    "#     df = spark.sql('select * from qx_testing.home_credit_installments_payments').toPandas()\n",
    "    df = read_table('installments_payments.csv')\n",
    "    \n",
    "    df['days_payment_diff'] = df['days_instalment']-df['days_entry_payment']\n",
    "    \n",
    "#     df['flag_pay_more'] = df.apply(lambda x:1 if (x['amt_payment']-x['amt_instalment'])>0 else 0 ,axis=1)\n",
    "\n",
    "    df = cal_mean(df,['sk_id_curr'],'days_instalment','group_instalment_mean')\n",
    "    df = cal_std(df,['sk_id_curr'],'days_instalment','group_instalment_std')\n",
    "    df = cal_mean(df,['sk_id_curr'],'amt_instalment','group_amt_instalment_mean')\n",
    "    df = cal_std(df,['sk_id_curr'],'amt_instalment','group_amt_instalment_std')\n",
    "    \n",
    "    paymet_agg={\n",
    "    'sk_id_prev': ['nunique'],\n",
    "    'days_payment_diff':['mean','max','min'],\n",
    "    'flag_pay_more':['mean','min','max'],\n",
    "    'group_instalment_mean':['mean'],\n",
    "    'group_instalment_std':['mean'],\n",
    "    'group_amt_instalment_mean':['mean'],\n",
    "    'group_amt_instalment_std':['mean'],\n",
    "    'num_instalment_version':['mean','max','min'],\n",
    "    'num_instalment_number':['mean','max','min'],\n",
    "    'amt_instalment':['mean','max','min'],\n",
    "    'amt_payment':['mean','max','min'],\n",
    "    'days_instalment':['mean','max','min'],\n",
    "    'days_entry_payment':['mean','max','min']\n",
    "    }\n",
    "    df = group(df,'instalment_',aggregations=paymet_agg)\n",
    "    \n",
    "    return df\n",
    "\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 衍生 credit_card表特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T06:38:55.378979Z",
     "start_time": "2019-03-29T06:38:55.355091Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_credit_card():\n",
    "#     df = spark.sql('select * from qx_testing.home_credit_credit_card_balance').toPandas()\n",
    "    df = read_table('credit_card_balance.csv')\n",
    "    df['limit_use'] = df['amt_balance']/df['amt_credit_limit_actual']\n",
    "    df['payment_div_min'] =  df['amt_payment_current'] - df['amt_inst_min_regularity']\n",
    "    df['late_payment'] = df['sk_dpd'].map(lambda x:1 if x>0 else 0)\n",
    "    \n",
    "    df['drawing_limit_ratio']= df['amt_drawings_atm_current']/df['amt_credit_limit_actual']\n",
    "    \n",
    "    credit_card_agg = {\n",
    "    'months_balance': ['min'],\n",
    "    'amt_balance': ['max'],\n",
    "    'amt_credit_limit_actual': ['max'],\n",
    "    'amt_drawings_atm_current': ['max', 'sum'],\n",
    "    'amt_drawings_current': ['max', 'sum'],\n",
    "    'amt_drawings_pos_current': ['max', 'sum'],\n",
    "    'amt_inst_min_regularity': ['max', 'mean'],\n",
    "    'amt_payment_total_current': ['max', 'mean', 'sum', 'var'],\n",
    "    'amt_total_receivable': ['max', 'mean'],\n",
    "    'cnt_drawings_atm_current': ['max', 'mean', 'sum'],\n",
    "    'cnt_drawings_current': ['max', 'mean', 'sum'],\n",
    "    'cnt_drawings_pos_current': ['mean'],\n",
    "    'sk_dpd': ['mean', 'max', 'sum'],\n",
    "    'sk_dpd_def': ['max', 'sum'],\n",
    "    'limit_use': ['max', 'mean'],\n",
    "    'payment_div_min': ['min', 'mean'],\n",
    "    'late_payment': ['max', 'sum'],\n",
    "    'drawing_limit_ratio':['mean','max','min']\n",
    "}\n",
    "\n",
    "    df = group(df,prefix='credit_card_',aggregations=credit_card_agg)\n",
    "    return df\n",
    "\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T07:01:54.249987Z",
     "start_time": "2019-03-29T07:01:54.240873Z"
    }
   },
   "outputs": [],
   "source": [
    "def main():\n",
    "    with timer(\"application_train and application_test\"):\n",
    "        df = process_train_test()\n",
    "        print(\"Application dataframe shape: \", df.shape)\n",
    "    with timer(\"Bureau and bureau_balance data\"):\n",
    "        bureau_df = process_bureau()\n",
    "        df = pd.merge(df, bureau_df, on='sk_id_curr', how='left')\n",
    "        print(\"Bureau dataframe shape: \", bureau_df.shape)\n",
    "        del bureau_df; gc.collect()\n",
    "    with timer(\"previous_application\"):\n",
    "        prev_df = process_previous_application()\n",
    "        df = pd.merge(df, prev_df, on='sk_id_curr', how='left')\n",
    "        print(\"Previous dataframe shape: \", prev_df.shape)\n",
    "        del prev_df; gc.collect()\n",
    "    with timer(\"pos_cash_balance\"):\n",
    "        pos_cash = process_pos_cash_balance()\n",
    "        df = pd.merge(df,pos_cash,on='sk_id_curr',how='left')\n",
    "        print(\"POS dataframe shape:\",pos_cash.shape)\n",
    "        del pos_cash;gc.collect()\n",
    "    with timer(\"installments_payment\"):\n",
    "        install_pay = process_payment()\n",
    "        df = pd.merge(df,install_pay,on='sk_id_curr',how='left')\n",
    "        print(\"Installment_payment shape :\",install_pay.shape)\n",
    "        del install_pay;gc.collect()\n",
    "    with timer(\"credit_card \"):\n",
    "        credit_card = process_credit_card()\n",
    "        df = pd.merge(df,credit_card,on='sk_id_curr',how='left')\n",
    "        print(\"Credit_card shape:\",credit_card.shape)\n",
    "        del credit_card;gc.collect()\n",
    "        \n",
    "    with timer(\"Run LightGBM\"):\n",
    "        feat_importance = kfold_lightgbm_sklearn(df)\n",
    "        print(feat_importance)\n",
    " \n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T07:01:56.311017Z",
     "start_time": "2019-03-29T07:01:56.304715Z"
    }
   },
   "outputs": [],
   "source": [
    "NUM_THREADS = 4\n",
    "DATA_DIRECTORY = \"../input/\"\n",
    "SUBMISSION_SUFIX = \"_model_3_29\"\n",
    "\n",
    "\n",
    "\n",
    "# 模型参数以及超参\n",
    "GENERATE_SUBMISSION_FILES = True\n",
    "STRATIFIED_KFOLD = False\n",
    "RANDOM_SEED = 737851\n",
    "NUM_FOLDS = 10\n",
    "EARLY_STOPPING = 100\n",
    "\n",
    "LIGHTGBM_PARAMS = {\n",
    "    'boosting_type': 'goss',\n",
    "    'n_estimators': 10000,\n",
    "        'learning_rate': 0.005134,\n",
    "    'num_leaves': 54,\n",
    "    'max_depth': 10,\n",
    "    'subsample_for_bin': 240000,\n",
    "    'reg_alpha': 0.436193,\n",
    "    'reg_lambda': 0.479169,\n",
    "    'colsample_bytree': 0.508716,\n",
    "    'min_split_gain': 0.024766,\n",
    "    'subsample': 1,\n",
    "    'is_unbalance': False,\n",
    "    'silent':-1,\n",
    "    'verbose':-1\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T08:10:52.264930Z",
     "start_time": "2019-03-29T08:10:52.251923Z"
    }
   },
   "outputs": [],
   "source": [
    "def kfold_lightgbm_sklearn(data, categorical_feature = None):\n",
    "    df = data[data['target'].notnull()]\n",
    "    test = data[data['target'].isnull()]\n",
    "    print(\"Train/valid shape: {}, test shape: {}\".format(df.shape, test.shape))\n",
    "    del_features = ['target', 'sk_id_curr', 'sk_id_bureau', 'sk_id_prev']\n",
    "    predictors = list(filter(lambda v: v not in del_features, df.columns))\n",
    "\n",
    "    if not STRATIFIED_KFOLD:\n",
    "        folds = KFold(n_splits= NUM_FOLDS, shuffle=True, random_state= RANDOM_SEED)\n",
    "    else:\n",
    "        folds = StratifiedKFold(n_splits= NUM_FOLDS, shuffle=True, random_state= RANDOM_SEED)\n",
    "\n",
    "    oof_preds = np.zeros(df.shape[0])\n",
    "    sub_preds = np.zeros(test.shape[0])\n",
    "    importance_df = pd.DataFrame()\n",
    "    eval_results = dict()\n",
    "\n",
    "    for n_fold, (train_idx, valid_idx) in enumerate(folds.split(df[predictors], df['target'])):\n",
    "        train_x, train_y = df[predictors].iloc[train_idx], df['target'].iloc[train_idx]\n",
    "        valid_x, valid_y = df[predictors].iloc[valid_idx], df['target'].iloc[valid_idx]\n",
    "\n",
    "        params = {'random_state': RANDOM_SEED, 'nthread': NUM_THREADS}\n",
    "        clf = LGBMClassifier(**{**params, **LIGHTGBM_PARAMS})\n",
    "        if not categorical_feature:\n",
    "            clf.fit(train_x, train_y, eval_set=[(train_x, train_y), (valid_x, valid_y)],\n",
    "                    eval_metric='auc', verbose=400, early_stopping_rounds= EARLY_STOPPING)\n",
    "        else:\n",
    "            clf.fit(train_x, train_y, eval_set=[(train_x, train_y), (valid_x, valid_y)],\n",
    "                    eval_metric='auc', verbose=400, early_stopping_rounds=EARLY_STOPPING,\n",
    "                    feature_name= list(df[predictors].columns), categorical_feature= categorical_feature)\n",
    "\n",
    "        oof_preds[valid_idx] = clf.predict_proba(valid_x, num_iteration=clf.best_iteration_)[:, 1]\n",
    "        sub_preds += clf.predict_proba(test[predictors], num_iteration=clf.best_iteration_)[:, 1] / folds.n_splits\n",
    "\n",
    "        fold_importance = pd.DataFrame()\n",
    "        fold_importance[\"feature\"] = predictors\n",
    "        fold_importance[\"gain\"] = clf.booster_.feature_importance(importance_type='gain')\n",
    "        fold_importance[\"split\"] = clf.booster_.feature_importance(importance_type='split')\n",
    "        importance_df = pd.concat([importance_df, fold_importance], axis=0)\n",
    "        eval_results['train_{}'.format(n_fold+1)]  = clf.evals_result_['training']['auc']\n",
    "        eval_results['valid_{}'.format(n_fold+1)] = clf.evals_result_['valid_1']['auc']\n",
    "\n",
    "        print('Fold %2d AUC : %.6f' % (n_fold + 1, roc_auc_score(valid_y, oof_preds[valid_idx])))\n",
    "        del clf, train_x, train_y, valid_x, valid_y\n",
    "        gc.collect()\n",
    "\n",
    "    print('Full AUC score %.6f' % roc_auc_score(df['target'], oof_preds))\n",
    "    test['target'] = sub_preds.copy()\n",
    "\n",
    "    mean_importance = importance_df.groupby('feature').mean().reset_index()\n",
    "    mean_importance.sort_values(by= 'gain', ascending=False, inplace=True)\n",
    "    if GENERATE_SUBMISSION_FILES:\n",
    "\n",
    "        oof = pd.DataFrame()\n",
    "        oof['sk_id_curr'] = df['sk_id_curr'].copy()\n",
    "        df['predictions'] = oof_preds.copy()\n",
    "        df['target'] = df['target'].copy()\n",
    "        df.to_csv('oof{}.csv'.format(SUBMISSION_SUFIX), index=False)\n",
    "        test[['sk_id_curr', 'target']].to_csv('submission{}.csv'.format(SUBMISSION_SUFIX), index=False)\n",
    "        mean_importance.to_csv('feature_importance{}.csv'.format(SUBMISSION_SUFIX), index=False)\n",
    "    return mean_importance\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T08:04:03.935141Z",
     "start_time": "2019-03-29T07:14:32.388394Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train/valid shape: (307506, 403), test shape: (48744, 403)\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.24001\ttraining's auc: 0.79058\tvalid_1's binary_logloss: 0.246077\tvalid_1's auc: 0.762525\n",
      "[800]\ttraining's binary_logloss: 0.228868\ttraining's auc: 0.816134\tvalid_1's binary_logloss: 0.240461\tvalid_1's auc: 0.775536\n",
      "[1200]\ttraining's binary_logloss: 0.221397\ttraining's auc: 0.834419\tvalid_1's binary_logloss: 0.238095\tvalid_1's auc: 0.781605\n",
      "[1600]\ttraining's binary_logloss: 0.215374\ttraining's auc: 0.849187\tvalid_1's binary_logloss: 0.236912\tvalid_1's auc: 0.784649\n",
      "[2000]\ttraining's binary_logloss: 0.210017\ttraining's auc: 0.862378\tvalid_1's binary_logloss: 0.236273\tvalid_1's auc: 0.786405\n",
      "[2400]\ttraining's binary_logloss: 0.205163\ttraining's auc: 0.87398\tvalid_1's binary_logloss: 0.235865\tvalid_1's auc: 0.787495\n",
      "[2800]\ttraining's binary_logloss: 0.200638\ttraining's auc: 0.884489\tvalid_1's binary_logloss: 0.235604\tvalid_1's auc: 0.788251\n",
      "[3200]\ttraining's binary_logloss: 0.196368\ttraining's auc: 0.894081\tvalid_1's binary_logloss: 0.235367\tvalid_1's auc: 0.788949\n",
      "[3600]\ttraining's binary_logloss: 0.192315\ttraining's auc: 0.902803\tvalid_1's binary_logloss: 0.235213\tvalid_1's auc: 0.789399\n",
      "[4000]\ttraining's binary_logloss: 0.188457\ttraining's auc: 0.910664\tvalid_1's binary_logloss: 0.23509\tvalid_1's auc: 0.789752\n",
      "[4400]\ttraining's binary_logloss: 0.184764\ttraining's auc: 0.917849\tvalid_1's binary_logloss: 0.234995\tvalid_1's auc: 0.79008\n",
      "Early stopping, best iteration is:\n",
      "[4667]\ttraining's binary_logloss: 0.182368\ttraining's auc: 0.92236\tvalid_1's binary_logloss: 0.234948\tvalid_1's auc: 0.790219\n",
      "Fold  1 AUC : 0.790219\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.239613\ttraining's auc: 0.790292\tvalid_1's binary_logloss: 0.250294\tvalid_1's auc: 0.760547\n",
      "[800]\ttraining's binary_logloss: 0.22849\ttraining's auc: 0.816016\tvalid_1's binary_logloss: 0.244541\tvalid_1's auc: 0.774048\n",
      "[1200]\ttraining's binary_logloss: 0.221071\ttraining's auc: 0.834211\tvalid_1's binary_logloss: 0.24214\tvalid_1's auc: 0.780258\n",
      "[1600]\ttraining's binary_logloss: 0.215013\ttraining's auc: 0.849179\tvalid_1's binary_logloss: 0.240811\tvalid_1's auc: 0.783743\n",
      "[2000]\ttraining's binary_logloss: 0.209654\ttraining's auc: 0.862405\tvalid_1's binary_logloss: 0.239992\tvalid_1's auc: 0.785927\n",
      "[2400]\ttraining's binary_logloss: 0.204753\ttraining's auc: 0.874174\tvalid_1's binary_logloss: 0.239537\tvalid_1's auc: 0.787022\n",
      "[2800]\ttraining's binary_logloss: 0.200222\ttraining's auc: 0.884702\tvalid_1's binary_logloss: 0.239226\tvalid_1's auc: 0.787788\n",
      "[3200]\ttraining's binary_logloss: 0.195942\ttraining's auc: 0.894225\tvalid_1's binary_logloss: 0.239001\tvalid_1's auc: 0.788385\n",
      "[3600]\ttraining's binary_logloss: 0.191889\ttraining's auc: 0.902992\tvalid_1's binary_logloss: 0.238812\tvalid_1's auc: 0.788856\n",
      "[4000]\ttraining's binary_logloss: 0.188006\ttraining's auc: 0.910979\tvalid_1's binary_logloss: 0.238715\tvalid_1's auc: 0.789144\n",
      "Early stopping, best iteration is:\n",
      "[4073]\ttraining's binary_logloss: 0.187317\ttraining's auc: 0.912345\tvalid_1's binary_logloss: 0.238691\tvalid_1's auc: 0.789215\n",
      "Fold  2 AUC : 0.789215\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.24078\ttraining's auc: 0.789678\tvalid_1's binary_logloss: 0.239492\tvalid_1's auc: 0.768833\n",
      "[800]\ttraining's binary_logloss: 0.22964\ttraining's auc: 0.815346\tvalid_1's binary_logloss: 0.233877\tvalid_1's auc: 0.781098\n",
      "[1200]\ttraining's binary_logloss: 0.22217\ttraining's auc: 0.833708\tvalid_1's binary_logloss: 0.231495\tvalid_1's auc: 0.787152\n",
      "[1600]\ttraining's binary_logloss: 0.216113\ttraining's auc: 0.848578\tvalid_1's binary_logloss: 0.230243\tvalid_1's auc: 0.790266\n",
      "[2000]\ttraining's binary_logloss: 0.210736\ttraining's auc: 0.861731\tvalid_1's binary_logloss: 0.229603\tvalid_1's auc: 0.791675\n",
      "[2400]\ttraining's binary_logloss: 0.205831\ttraining's auc: 0.873545\tvalid_1's binary_logloss: 0.229216\tvalid_1's auc: 0.792582\n",
      "[2800]\ttraining's binary_logloss: 0.201251\ttraining's auc: 0.884242\tvalid_1's binary_logloss: 0.228924\tvalid_1's auc: 0.793408\n",
      "[3200]\ttraining's binary_logloss: 0.196969\ttraining's auc: 0.893823\tvalid_1's binary_logloss: 0.228776\tvalid_1's auc: 0.793684\n",
      "[3600]\ttraining's binary_logloss: 0.192907\ttraining's auc: 0.90242\tvalid_1's binary_logloss: 0.228602\tvalid_1's auc: 0.794116\n",
      "Early stopping, best iteration is:\n",
      "[3694]\ttraining's binary_logloss: 0.191983\ttraining's auc: 0.904379\tvalid_1's binary_logloss: 0.228531\tvalid_1's auc: 0.79428\n",
      "Fold  3 AUC : 0.794280\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.239334\ttraining's auc: 0.790209\tvalid_1's binary_logloss: 0.252269\tvalid_1's auc: 0.765147\n",
      "[800]\ttraining's binary_logloss: 0.228196\ttraining's auc: 0.816131\tvalid_1's binary_logloss: 0.246251\tvalid_1's auc: 0.778702\n",
      "[1200]\ttraining's binary_logloss: 0.220789\ttraining's auc: 0.83409\tvalid_1's binary_logloss: 0.243766\tvalid_1's auc: 0.785025\n",
      "[1600]\ttraining's binary_logloss: 0.214696\ttraining's auc: 0.849144\tvalid_1's binary_logloss: 0.242351\tvalid_1's auc: 0.788966\n",
      "[2000]\ttraining's binary_logloss: 0.20937\ttraining's auc: 0.86224\tvalid_1's binary_logloss: 0.241566\tvalid_1's auc: 0.791156\n",
      "[2400]\ttraining's binary_logloss: 0.204486\ttraining's auc: 0.874078\tvalid_1's binary_logloss: 0.241058\tvalid_1's auc: 0.792558\n",
      "[2800]\ttraining's binary_logloss: 0.199964\ttraining's auc: 0.88466\tvalid_1's binary_logloss: 0.240709\tvalid_1's auc: 0.793576\n",
      "[3200]\ttraining's binary_logloss: 0.195718\ttraining's auc: 0.894222\tvalid_1's binary_logloss: 0.24041\tvalid_1's auc: 0.794451\n",
      "[3600]\ttraining's binary_logloss: 0.191669\ttraining's auc: 0.903018\tvalid_1's binary_logloss: 0.240227\tvalid_1's auc: 0.795068\n",
      "[4000]\ttraining's binary_logloss: 0.187815\ttraining's auc: 0.910953\tvalid_1's binary_logloss: 0.240116\tvalid_1's auc: 0.795444\n",
      "Early stopping, best iteration is:\n",
      "[4227]\ttraining's binary_logloss: 0.185718\ttraining's auc: 0.915128\tvalid_1's binary_logloss: 0.240046\tvalid_1's auc: 0.795647\n",
      "Fold  4 AUC : 0.795647\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.23955\ttraining's auc: 0.790806\tvalid_1's binary_logloss: 0.250588\tvalid_1's auc: 0.758444\n",
      "[800]\ttraining's binary_logloss: 0.228371\ttraining's auc: 0.816434\tvalid_1's binary_logloss: 0.244896\tvalid_1's auc: 0.772115\n",
      "[1200]\ttraining's binary_logloss: 0.220947\ttraining's auc: 0.83459\tvalid_1's binary_logloss: 0.242424\tvalid_1's auc: 0.778977\n",
      "[1600]\ttraining's binary_logloss: 0.214907\ttraining's auc: 0.84944\tvalid_1's binary_logloss: 0.241094\tvalid_1's auc: 0.782811\n",
      "[2000]\ttraining's binary_logloss: 0.209585\ttraining's auc: 0.862363\tvalid_1's binary_logloss: 0.240325\tvalid_1's auc: 0.785036\n",
      "[2400]\ttraining's binary_logloss: 0.204738\ttraining's auc: 0.873972\tvalid_1's binary_logloss: 0.23976\tvalid_1's auc: 0.786623\n",
      "[2800]\ttraining's binary_logloss: 0.200194\ttraining's auc: 0.884585\tvalid_1's binary_logloss: 0.239472\tvalid_1's auc: 0.787277\n",
      "[3200]\ttraining's binary_logloss: 0.19588\ttraining's auc: 0.894421\tvalid_1's binary_logloss: 0.239295\tvalid_1's auc: 0.787677\n",
      "[3600]\ttraining's binary_logloss: 0.191805\ttraining's auc: 0.903167\tvalid_1's binary_logloss: 0.239175\tvalid_1's auc: 0.788042\n",
      "Early stopping, best iteration is:\n",
      "[3840]\ttraining's binary_logloss: 0.189455\ttraining's auc: 0.908082\tvalid_1's binary_logloss: 0.239066\tvalid_1's auc: 0.788343\n",
      "Fold  5 AUC : 0.788343\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.239986\ttraining's auc: 0.789499\tvalid_1's binary_logloss: 0.247987\tvalid_1's auc: 0.766881\n",
      "[800]\ttraining's binary_logloss: 0.228844\ttraining's auc: 0.815458\tvalid_1's binary_logloss: 0.241768\tvalid_1's auc: 0.781082\n",
      "[1200]\ttraining's binary_logloss: 0.221364\ttraining's auc: 0.833945\tvalid_1's binary_logloss: 0.239086\tvalid_1's auc: 0.788016\n",
      "[1600]\ttraining's binary_logloss: 0.215329\ttraining's auc: 0.848818\tvalid_1's binary_logloss: 0.237735\tvalid_1's auc: 0.791427\n",
      "[2000]\ttraining's binary_logloss: 0.209997\ttraining's auc: 0.86191\tvalid_1's binary_logloss: 0.236885\tvalid_1's auc: 0.79362\n",
      "[2400]\ttraining's binary_logloss: 0.205091\ttraining's auc: 0.873736\tvalid_1's binary_logloss: 0.236374\tvalid_1's auc: 0.794821\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2800]\ttraining's binary_logloss: 0.20053\ttraining's auc: 0.884434\tvalid_1's binary_logloss: 0.236005\tvalid_1's auc: 0.795783\n",
      "[3200]\ttraining's binary_logloss: 0.196255\ttraining's auc: 0.893917\tvalid_1's binary_logloss: 0.235703\tvalid_1's auc: 0.796537\n",
      "[3600]\ttraining's binary_logloss: 0.192221\ttraining's auc: 0.902636\tvalid_1's binary_logloss: 0.235532\tvalid_1's auc: 0.797011\n",
      "[4000]\ttraining's binary_logloss: 0.188388\ttraining's auc: 0.910428\tvalid_1's binary_logloss: 0.235398\tvalid_1's auc: 0.797301\n",
      "[4400]\ttraining's binary_logloss: 0.18467\ttraining's auc: 0.91775\tvalid_1's binary_logloss: 0.235293\tvalid_1's auc: 0.797477\n",
      "Early stopping, best iteration is:\n",
      "[4347]\ttraining's binary_logloss: 0.185153\ttraining's auc: 0.916826\tvalid_1's binary_logloss: 0.23528\tvalid_1's auc: 0.797539\n",
      "Fold  6 AUC : 0.797539\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.240132\ttraining's auc: 0.789863\tvalid_1's binary_logloss: 0.245636\tvalid_1's auc: 0.767506\n",
      "[800]\ttraining's binary_logloss: 0.228926\ttraining's auc: 0.815978\tvalid_1's binary_logloss: 0.239822\tvalid_1's auc: 0.78038\n",
      "[1200]\ttraining's binary_logloss: 0.221457\ttraining's auc: 0.834092\tvalid_1's binary_logloss: 0.237342\tvalid_1's auc: 0.786519\n",
      "[1600]\ttraining's binary_logloss: 0.215413\ttraining's auc: 0.848898\tvalid_1's binary_logloss: 0.236173\tvalid_1's auc: 0.789345\n",
      "[2000]\ttraining's binary_logloss: 0.210089\ttraining's auc: 0.861871\tvalid_1's binary_logloss: 0.235509\tvalid_1's auc: 0.790892\n",
      "[2400]\ttraining's binary_logloss: 0.205238\ttraining's auc: 0.873538\tvalid_1's binary_logloss: 0.235115\tvalid_1's auc: 0.791952\n",
      "[2800]\ttraining's binary_logloss: 0.200704\ttraining's auc: 0.884145\tvalid_1's binary_logloss: 0.234921\tvalid_1's auc: 0.792414\n",
      "[3200]\ttraining's binary_logloss: 0.196417\ttraining's auc: 0.893856\tvalid_1's binary_logloss: 0.234754\tvalid_1's auc: 0.792881\n",
      "Early stopping, best iteration is:\n",
      "[3440]\ttraining's binary_logloss: 0.193939\ttraining's auc: 0.899318\tvalid_1's binary_logloss: 0.234661\tvalid_1's auc: 0.793187\n",
      "Fold  7 AUC : 0.793187\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.239683\ttraining's auc: 0.79054\tvalid_1's binary_logloss: 0.249606\tvalid_1's auc: 0.761268\n",
      "[800]\ttraining's binary_logloss: 0.228541\ttraining's auc: 0.816065\tvalid_1's binary_logloss: 0.243717\tvalid_1's auc: 0.775036\n",
      "[1200]\ttraining's binary_logloss: 0.221136\ttraining's auc: 0.834127\tvalid_1's binary_logloss: 0.241122\tvalid_1's auc: 0.782238\n",
      "[1600]\ttraining's binary_logloss: 0.215119\ttraining's auc: 0.848851\tvalid_1's binary_logloss: 0.239833\tvalid_1's auc: 0.785803\n",
      "[2000]\ttraining's binary_logloss: 0.209786\ttraining's auc: 0.861872\tvalid_1's binary_logloss: 0.239039\tvalid_1's auc: 0.788163\n",
      "[2400]\ttraining's binary_logloss: 0.204912\ttraining's auc: 0.873601\tvalid_1's binary_logloss: 0.238499\tvalid_1's auc: 0.789792\n",
      "[2800]\ttraining's binary_logloss: 0.20034\ttraining's auc: 0.884185\tvalid_1's binary_logloss: 0.238141\tvalid_1's auc: 0.790845\n",
      "[3200]\ttraining's binary_logloss: 0.196092\ttraining's auc: 0.893639\tvalid_1's binary_logloss: 0.237923\tvalid_1's auc: 0.791504\n",
      "[3600]\ttraining's binary_logloss: 0.192037\ttraining's auc: 0.902366\tvalid_1's binary_logloss: 0.237742\tvalid_1's auc: 0.791983\n",
      "[4000]\ttraining's binary_logloss: 0.188182\ttraining's auc: 0.910309\tvalid_1's binary_logloss: 0.237591\tvalid_1's auc: 0.792447\n",
      "Early stopping, best iteration is:\n",
      "[3989]\ttraining's binary_logloss: 0.188282\ttraining's auc: 0.910118\tvalid_1's binary_logloss: 0.237582\tvalid_1's auc: 0.792475\n",
      "Fold  8 AUC : 0.792475\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.240418\ttraining's auc: 0.789627\tvalid_1's binary_logloss: 0.243312\tvalid_1's auc: 0.770089\n",
      "[800]\ttraining's binary_logloss: 0.229252\ttraining's auc: 0.815587\tvalid_1's binary_logloss: 0.23776\tvalid_1's auc: 0.781314\n",
      "[1200]\ttraining's binary_logloss: 0.221789\ttraining's auc: 0.833781\tvalid_1's binary_logloss: 0.23551\tvalid_1's auc: 0.78654\n",
      "[1600]\ttraining's binary_logloss: 0.215755\ttraining's auc: 0.848532\tvalid_1's binary_logloss: 0.234377\tvalid_1's auc: 0.789308\n",
      "[2000]\ttraining's binary_logloss: 0.210422\ttraining's auc: 0.861482\tvalid_1's binary_logloss: 0.233725\tvalid_1's auc: 0.790997\n",
      "[2400]\ttraining's binary_logloss: 0.205517\ttraining's auc: 0.873297\tvalid_1's binary_logloss: 0.233286\tvalid_1's auc: 0.792146\n",
      "[2800]\ttraining's binary_logloss: 0.200927\ttraining's auc: 0.884184\tvalid_1's binary_logloss: 0.233031\tvalid_1's auc: 0.792757\n",
      "[3200]\ttraining's binary_logloss: 0.196672\ttraining's auc: 0.893842\tvalid_1's binary_logloss: 0.232846\tvalid_1's auc: 0.793288\n",
      "[3600]\ttraining's binary_logloss: 0.192611\ttraining's auc: 0.902566\tvalid_1's binary_logloss: 0.232698\tvalid_1's auc: 0.793648\n",
      "[4000]\ttraining's binary_logloss: 0.188701\ttraining's auc: 0.910482\tvalid_1's binary_logloss: 0.23256\tvalid_1's auc: 0.79398\n",
      "Early stopping, best iteration is:\n",
      "[3999]\ttraining's binary_logloss: 0.188711\ttraining's auc: 0.910454\tvalid_1's binary_logloss: 0.23256\tvalid_1's auc: 0.793977\n",
      "Fold  9 AUC : 0.793977\n",
      "Training until validation scores don't improve for 100 rounds.\n",
      "[400]\ttraining's binary_logloss: 0.240831\ttraining's auc: 0.789949\tvalid_1's binary_logloss: 0.238882\tvalid_1's auc: 0.774137\n",
      "[800]\ttraining's binary_logloss: 0.229632\ttraining's auc: 0.815757\tvalid_1's binary_logloss: 0.233564\tvalid_1's auc: 0.783701\n",
      "[1200]\ttraining's binary_logloss: 0.222162\ttraining's auc: 0.833868\tvalid_1's binary_logloss: 0.231475\tvalid_1's auc: 0.788453\n",
      "[1600]\ttraining's binary_logloss: 0.216086\ttraining's auc: 0.848823\tvalid_1's binary_logloss: 0.23045\tvalid_1's auc: 0.790908\n",
      "[2000]\ttraining's binary_logloss: 0.210701\ttraining's auc: 0.861948\tvalid_1's binary_logloss: 0.229833\tvalid_1's auc: 0.792515\n",
      "[2400]\ttraining's binary_logloss: 0.205788\ttraining's auc: 0.873694\tvalid_1's binary_logloss: 0.229471\tvalid_1's auc: 0.793486\n",
      "[2800]\ttraining's binary_logloss: 0.201212\ttraining's auc: 0.884379\tvalid_1's binary_logloss: 0.229176\tvalid_1's auc: 0.794293\n",
      "[3200]\ttraining's binary_logloss: 0.196902\ttraining's auc: 0.894054\tvalid_1's binary_logloss: 0.228996\tvalid_1's auc: 0.794729\n",
      "[3600]\ttraining's binary_logloss: 0.192832\ttraining's auc: 0.902748\tvalid_1's binary_logloss: 0.228844\tvalid_1's auc: 0.795128\n",
      "[4000]\ttraining's binary_logloss: 0.188964\ttraining's auc: 0.910577\tvalid_1's binary_logloss: 0.228714\tvalid_1's auc: 0.795438\n",
      "Early stopping, best iteration is:\n",
      "[4161]\ttraining's binary_logloss: 0.187443\ttraining's auc: 0.913626\tvalid_1's binary_logloss: 0.228681\tvalid_1's auc: 0.795502\n",
      "Fold 10 AUC : 0.795502\n",
      "Full AUC score 0.792998\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>feature</th>\n",
       "      <th>gain</th>\n",
       "      <th>split</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>ext_sources_mean</td>\n",
       "      <td>771262.361903</td>\n",
       "      <td>2858.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>ext_sources_min</td>\n",
       "      <td>276381.822786</td>\n",
       "      <td>2314.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>ext_sources_max</td>\n",
       "      <td>179075.527504</td>\n",
       "      <td>2006.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>ext_source_3</td>\n",
       "      <td>142918.488955</td>\n",
       "      <td>3011.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>credit_to_annuity_ratio</td>\n",
       "      <td>128053.027334</td>\n",
       "      <td>4033.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>credit_to_goods_ratio</td>\n",
       "      <td>90546.955924</td>\n",
       "      <td>2013.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>ext_source_2</td>\n",
       "      <td>73748.915337</td>\n",
       "      <td>2056.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>days_birth</td>\n",
       "      <td>64032.486006</td>\n",
       "      <td>2681.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>amt_annuity</td>\n",
       "      <td>60778.280872</td>\n",
       "      <td>2371.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>instalment_days_payment_diff_min</td>\n",
       "      <td>60723.477559</td>\n",
       "      <td>2004.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>days_employed</td>\n",
       "      <td>59432.574654</td>\n",
       "      <td>1954.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>ext_sources_prod</td>\n",
       "      <td>59114.899518</td>\n",
       "      <td>1604.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>bureau_days_credit_max</td>\n",
       "      <td>58833.643559</td>\n",
       "      <td>2598.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355</th>\n",
       "      <td>prev_days_last_due_1st_version_max</td>\n",
       "      <td>55363.355569</td>\n",
       "      <td>2279.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>instalment_group_instalment_std_mean</td>\n",
       "      <td>53732.209251</td>\n",
       "      <td>1692.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>bureau_amt_credit_sum_debt_mean</td>\n",
       "      <td>51412.016661</td>\n",
       "      <td>2196.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>ext_source_1</td>\n",
       "      <td>49750.517949</td>\n",
       "      <td>2069.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>employed_to_birth_ratio</td>\n",
       "      <td>47783.564428</td>\n",
       "      <td>1902.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>bureau_days_credit_enddate_max</td>\n",
       "      <td>47472.363768</td>\n",
       "      <td>2444.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>instalment_amt_payment_min</td>\n",
       "      <td>41605.657701</td>\n",
       "      <td>1657.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>amt_goods_price</td>\n",
       "      <td>39599.023498</td>\n",
       "      <td>1666.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>group_ext_sources_std</td>\n",
       "      <td>39436.245032</td>\n",
       "      <td>1719.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>annuity_to_income_ratio</td>\n",
       "      <td>38695.136719</td>\n",
       "      <td>1835.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>days_id_publish</td>\n",
       "      <td>37696.430522</td>\n",
       "      <td>2030.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>days_last_phone_change</td>\n",
       "      <td>37552.305448</td>\n",
       "      <td>2158.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>bureau_days_credit_mean</td>\n",
       "      <td>36596.390792</td>\n",
       "      <td>1686.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>income_to_employed_ratio</td>\n",
       "      <td>36147.277139</td>\n",
       "      <td>1761.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <td>pos_cash_months_balance_size</td>\n",
       "      <td>36123.387792</td>\n",
       "      <td>1559.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>prev_application_credit_diff_max</td>\n",
       "      <td>35973.219925</td>\n",
       "      <td>1265.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>group_ext_sources_median</td>\n",
       "      <td>35445.749913</td>\n",
       "      <td>1614.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>credit_card_late_payment_max</td>\n",
       "      <td>25.447680</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>name_type_suite_Other_A</td>\n",
       "      <td>21.256860</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283</th>\n",
       "      <td>organization_type_Mobile</td>\n",
       "      <td>17.503331</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275</th>\n",
       "      <td>organization_type_Industry: type 7</td>\n",
       "      <td>16.210340</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>organization_type_Industry: type 5</td>\n",
       "      <td>15.686045</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td>wallsmaterial_mode_Monolithic</td>\n",
       "      <td>15.517158</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>organization_type_Industry: type 10</td>\n",
       "      <td>12.186340</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>organization_type_Emergency</td>\n",
       "      <td>6.537150</td>\n",
       "      <td>0.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>307</th>\n",
       "      <td>organization_type_University</td>\n",
       "      <td>6.155577</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>organization_type_Transport: type 1</td>\n",
       "      <td>5.885404</td>\n",
       "      <td>0.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260</th>\n",
       "      <td>organization_type_Electricity</td>\n",
       "      <td>3.705686</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>organization_type_Industry: type 2</td>\n",
       "      <td>3.021680</td>\n",
       "      <td>0.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>name_type_suite_Group of people</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>274</th>\n",
       "      <td>organization_type_Industry: type 6</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288</th>\n",
       "      <td>organization_type_Religion</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>organization_type_Trade: type 1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>organization_type_Trade: type 4</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>organization_type_Trade: type 5</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>name_income_type_Businessman</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>name_income_type_Maternity leave</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>organization_type_Industry: type 13</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>name_family_status_Unknown</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>instalment_flag_pay_more_min</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>organization_type_Culture</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>organization_type_Insurance</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>name_income_type_Student</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>name_income_type_Unemployed</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>flag_mobil</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240</th>\n",
       "      <td>occupation_type_IT staff</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276</th>\n",
       "      <td>organization_type_Industry: type 8</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>401 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  feature           gain   split\n",
       "114                      ext_sources_mean  771262.361903  2858.8\n",
       "115                       ext_sources_min  276381.822786  2314.1\n",
       "113                       ext_sources_max  179075.527504  2006.4\n",
       "111                          ext_source_3  142918.488955  3011.6\n",
       "89                credit_to_annuity_ratio  128053.027334  4033.4\n",
       "90                  credit_to_goods_ratio   90546.955924  2013.5\n",
       "110                          ext_source_2   73748.915337  2056.1\n",
       "92                             days_birth   64032.486006  2681.5\n",
       "1                             amt_annuity   60778.280872  2371.5\n",
       "165      instalment_days_payment_diff_min   60723.477559  2004.2\n",
       "93                          days_employed   59432.574654  1954.3\n",
       "116                      ext_sources_prod   59114.899518  1604.0\n",
       "33                 bureau_days_credit_max   58833.643559  2598.4\n",
       "355    prev_days_last_due_1st_version_max   55363.355569  2279.0\n",
       "172  instalment_group_instalment_std_mean   53732.209251  1692.1\n",
       "21        bureau_amt_credit_sum_debt_mean   51412.016661  2196.5\n",
       "109                          ext_source_1   49750.517949  2069.7\n",
       "105               employed_to_birth_ratio   47783.564428  1902.3\n",
       "31         bureau_days_credit_enddate_max   47472.363768  2444.8\n",
       "156            instalment_amt_payment_min   41605.657701  1657.1\n",
       "3                         amt_goods_price   39599.023498  1666.7\n",
       "142                 group_ext_sources_std   39436.245032  1719.1\n",
       "11                annuity_to_income_ratio   38695.136719  1835.1\n",
       "94                        days_id_publish   37696.430522  2030.5\n",
       "95                 days_last_phone_change   37552.305448  2158.2\n",
       "34                bureau_days_credit_mean   36596.390792  1686.9\n",
       "150              income_to_employed_ratio   36147.277139  1761.6\n",
       "320          pos_cash_months_balance_size   36123.387792  1559.1\n",
       "338      prev_application_credit_diff_max   35973.219925  1265.6\n",
       "141              group_ext_sources_median   35445.749913  1614.3\n",
       "..                                    ...            ...     ...\n",
       "77           credit_card_late_payment_max      25.447680     1.4\n",
       "221               name_type_suite_Other_A      21.256860     1.5\n",
       "283              organization_type_Mobile      17.503331     1.2\n",
       "275    organization_type_Industry: type 7      16.210340     1.1\n",
       "273    organization_type_Industry: type 5      15.686045     1.7\n",
       "383         wallsmaterial_mode_Monolithic      15.517158     1.2\n",
       "266   organization_type_Industry: type 10      12.186340     0.6\n",
       "261           organization_type_Emergency       6.537150     0.4\n",
       "307          organization_type_University       6.155577     0.5\n",
       "303   organization_type_Transport: type 1       5.885404     0.7\n",
       "260         organization_type_Electricity       3.705686     0.3\n",
       "270    organization_type_Industry: type 2       3.021680     0.4\n",
       "220       name_type_suite_Group of people       0.000000     0.0\n",
       "274    organization_type_Industry: type 6       0.000000     0.0\n",
       "288            organization_type_Religion       0.000000     0.0\n",
       "296       organization_type_Trade: type 1       0.000000     0.0\n",
       "299       organization_type_Trade: type 4       0.000000     0.0\n",
       "300       organization_type_Trade: type 5       0.000000     0.0\n",
       "210          name_income_type_Businessman       0.000000     0.0\n",
       "212      name_income_type_Maternity leave       0.000000     0.0\n",
       "269   organization_type_Industry: type 13       0.000000     0.0\n",
       "202            name_family_status_Unknown       0.000000     0.0\n",
       "168          instalment_flag_pay_more_min       0.000000     0.0\n",
       "259             organization_type_Culture       0.000000     0.0\n",
       "278           organization_type_Insurance       0.000000     0.0\n",
       "215              name_income_type_Student       0.000000     0.0\n",
       "216           name_income_type_Unemployed       0.000000     0.0\n",
       "120                            flag_mobil       0.000000     0.0\n",
       "240              occupation_type_IT staff       0.000000     0.0\n",
       "276    organization_type_Industry: type 8       0.000000     0.0\n",
       "\n",
       "[401 rows x 3 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kfold_lightgbm_sklearn(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-03-29T10:03:42.417366Z",
     "start_time": "2019-03-29T10:03:42.410525Z"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "def write_to_hive(df):\n",
    "    train = df[df['target'].notnull()].astype(str)\n",
    "    test = df[df['target'].isnull()].astype(str)\n",
    "    print(\"-----正在创建train DataFrame----\")\n",
    "    t0 = time.time()\n",
    "    hive_train = spark.createDataFrame(train)\n",
    "    hive_train.write.saveAsTable(name='qx_testing.feature_train',mode='overwrite',partitionBy=None)\n",
    "    print(\"---train 写入hive success！耗时 %.5f s\"%(time.time()-t0))\n",
    "    print('\\n')\n",
    "    print(\"-----正在创建train DataFrame----\")\n",
    "    t1 = time.time()\n",
    "    hive_test = spark.createDataFrame(test)\n",
    "    hive_test.write.saveAsTable(name='qx_testing.feature_test',mode='overwrite',partitionBy=None)\n",
    "    print(\"---test 写入hive success！耗时 %.5f s\"%(time.time()-t1))\n",
    "    del train,test,hive_train,hive_test;gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
